﻿print 'Update v1.24'
go

print 'ALTER PROCEDURE dbo.RepAnalizKpoPol'
go

ALTER PROCEDURE dbo.RepAnalizKpoPol
(
	@D_S datetime,
	@D_PO datetime, 
	@K_SPEC int,
	@K_RAB int,
	@K_ANALIZ_KPO int,
	@K_RAZDEL int
)
AS
	-- Анализ КПО - Распределение по полу
BEGIN
SET STATISTICS XML OFF
INSERT INTO bo_analiz_kpo_
                      (C_NE_PROVED_DEN, C_NE_PROVED_HOLIDAY, C_NE_PROVED_NOCH, C_PROVED_DEN, C_PROVED_NOCH, C_PROVED_HOLIDAY, K_RAZDEL, 
                      K_ANALIZ_KPO, N_NAME)
SELECT     SUM(RepAnalizKpo.NeProvedDen) AS NeProvedDen, SUM(RepAnalizKpo.NeProvedHoliday) AS NeProvedHoliday, 
                      SUM(RepAnalizKpo.NeProvedNoch) AS NeProvedNoch, SUM(RepAnalizKpo.ProvedDen) AS ProvedDen, SUM(RepAnalizKpo.ProvedNoch) 
                      AS ProvedNoch, SUM(RepAnalizKpo.ProvedHoliday) AS ProvedHoliday, @K_RAZDEL AS K_RAZDEL, @K_ANALIZ_KPO AS K_ANALIZ_KPO, 
                      N_POL_FULL_PADEG
FROM         dbo.RepAnalizKpo_(@D_S, @D_PO, @K_SPEC, @K_RAB) AS RepAnalizKpo INNER JOIN
                      b_kpo ON RepAnalizKpo.K_KPO = b_kpo.K_KPO LEFT OUTER JOIN
                      s_pol ON b_kpo.K_POL = s_pol.K_POL
GROUP BY N_POL_FULL_PADEG 
ORDER BY N_POL_FULL_PADEG
	RETURN
END

go
print 'ALTER PROCEDURE dbo.RepAnalizKpoVozrastDeti'
go


ALTER PROCEDURE dbo.RepAnalizKpoVozrastDeti
(
	@D_S datetime,
	@D_PO datetime, 
	@K_SPEC int,
	@K_RAB int,
	@K_ANALIZ_KPO int,
	@K_RAZDEL int
)
AS
	-- Анализ КПО - Распределение по возрасту детей
BEGIN
SET STATISTICS XML OFF
INSERT INTO bo_analiz_kpo_
                      (C_NE_PROVED_DEN, C_NE_PROVED_HOLIDAY, C_NE_PROVED_NOCH, C_PROVED_DEN, C_PROVED_NOCH, C_PROVED_HOLIDAY, K_RAZDEL, 
                      K_ANALIZ_KPO, N_NAME)
SELECT     SUM(RepAnalizKpo.NeProvedDen) AS NeProvedDen, SUM(RepAnalizKpo.NeProvedHoliday) AS NeProvedHoliday, 
           SUM(RepAnalizKpo.NeProvedNoch) AS NeProvedNoch, SUM(RepAnalizKpo.ProvedDen) AS ProvedDen, SUM(RepAnalizKpo.ProvedNoch) AS ProvedNoch, 
			SUM(RepAnalizKpo.ProvedHoliday) AS ProvedHoliday, @K_RAZDEL AS K_RAZDEL, @K_ANALIZ_KPO AS K_ANALIZ_KPO, s_period_let_deti.N_PERIOD
FROM         b_sostav_semyi_kpo 
				INNER JOIN dbo.RepAnalizKpo_(@D_S, @D_PO, @K_SPEC, @K_RAB) as RepAnalizKpo ON b_sostav_semyi_kpo.K_KPO = RepAnalizKpo.K_KPO 
				INNER JOIN s_period_let_deti ON YEAR(GETDATE()) - b_sostav_semyi_kpo.GOD_R <= s_period_let_deti.C_END AND YEAR(GETDATE()) - b_sostav_semyi_kpo.GOD_R >= s_period_let_deti.C_START or isnull(b_sostav_semyi_kpo.GOD_R, '-1') = s_period_let_deti.C_START
WHERE     (b_sostav_semyi_kpo.K_RODSTV_OTN BETWEEN 8 AND 13) OR (b_sostav_semyi_kpo.K_RODSTV_OTN BETWEEN 22 AND 23)
GROUP BY s_period_let_deti.N_PERIOD, s_period_let_deti.C_START
ORDER BY s_period_let_deti.C_START
	RETURN
END


go
print 'insert into s_period_let_deti'
go
set IDENTITY_INSERT s_period_let_deti ON
insert into s_period_let_deti(K_PERIOD_LET, C_START, C_END, N_PERIOD) VALUES (7, -1, -1, 'Не задан')
set IDENTITY_INSERT s_period_let_deti OFF


go
print 'ALTER PROCEDURE RepAnalizKpoVozrast'
go

ALTER PROCEDURE dbo.RepAnalizKpoVozrast
(
	@D_S datetime,
	@D_PO datetime, 
	@K_SPEC int,
	@K_RAB int,
	@K_ANALIZ_KPO int,
	@K_RAZDEL int
)
AS
	-- Анализ КПО - Распределение по возрасту
BEGIN
SET STATISTICS XML OFF
INSERT INTO bo_analiz_kpo_
                      (C_NE_PROVED_DEN, C_NE_PROVED_HOLIDAY, C_NE_PROVED_NOCH, C_PROVED_DEN, C_PROVED_NOCH, C_PROVED_HOLIDAY, K_RAZDEL, 
                      K_ANALIZ_KPO, N_NAME)
SELECT     SUM(RepAnalizKpo.NeProvedDen) AS NeProvedDen, SUM(RepAnalizKpo.NeProvedHoliday) AS NeProvedHoliday, 
                      SUM(RepAnalizKpo.NeProvedNoch) AS NeProvedNoch, SUM(RepAnalizKpo.ProvedDen) AS ProvedDen, SUM(RepAnalizKpo.ProvedNoch) AS ProvedNoch,
                      SUM(RepAnalizKpo.ProvedHoliday) AS ProvedHoliday, @K_RAZDEL AS K_RAZDEL, @K_ANALIZ_KPO AS K_ANALIZ_KPO, 
                      s_period_let.N_PERIOD
FROM         b_kpo 
						INNER JOIN dbo.RepAnalizKpo_(@D_S, @D_PO, @K_SPEC, @K_RAB) AS RepAnalizKpo ON b_kpo.K_KPO = RepAnalizKpo.K_KPO INNER JOIN
                      s_period_let ON YEAR(GETDATE()) - b_kpo.GOD_R <= s_period_let.C_END AND YEAR(GETDATE()) - b_kpo.GOD_R >= s_period_let.C_START or isnull(b_kpo.GOD_R, '-1') = s_period_let.C_START
GROUP BY s_period_let.N_PERIOD, s_period_let.C_START
ORDER BY s_period_let.C_START
	RETURN
END